﻿Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Xml


Public Class clsExcelWriter
    Implements IDisposable
    Private _writer As XmlWriter

    Public Enum CellStyle
        General
        Number
        Currency
        DateTime
        ShortDate
    End Enum
    Const Const_OFFICE = "urn:schemas-microsoft-com:office:office"
    Const Const_SHEET = "urn:schemas-microsoft-com:office:spreadsheet"
    Const Const_EXCEL = "urn:schemas-microsoft-com:office:excel"


    Private Sub WriteExcelStyleElement(style As CellStyle)
        _writer.WriteStartElement("Style")
        _writer.WriteAttributeString("ID", Const_SHEET, style.ToString())
        _writer.WriteEndElement()
    End Sub

    Private Sub WriteExcelStyleElement(style As CellStyle, NumberFormat As String)
        _writer.WriteStartElement("Style")

        _writer.WriteAttributeString("ID", Const_SHEET, style.ToString())
        _writer.WriteStartElement("NumberFormat")
        _writer.WriteAttributeString("Format", Const_SHEET, NumberFormat)
        _writer.WriteEndElement()

        _writer.WriteEndElement()

    End Sub

    Private Sub WriteExcelStyles()
        _writer.WriteStartElement("Styles")
        WriteExcelStyleElement(CellStyle.General)
        WriteExcelStyleElement(CellStyle.Number, "General Number")
        WriteExcelStyleElement(CellStyle.DateTime, "General Date")
        WriteExcelStyleElement(CellStyle.Currency, "Currency")
        WriteExcelStyleElement(CellStyle.ShortDate, "Short Date")
        _writer.WriteEndElement()
    End Sub

    Public Sub New(outputFileName As String)
        Dim settings As New XmlWriterSettings()
        settings.Indent = True
        _writer = XmlWriter.Create(outputFileName, settings)
    End Sub

    Public Sub Close()
        If _writer Is Nothing Then
            Throw New InvalidOperationException("Already closed.")
        End If
        _writer.Close()
        _writer = Nothing
    End Sub

    Public Sub WriteExcelColumnDefinition(columnWidth As Integer)
        If _writer Is Nothing Then
            Throw New InvalidOperationException("Cannot write after closing.")
        End If
        _writer.WriteStartElement("Column", Const_SHEET)
        _writer.WriteStartAttribute("Width", Const_SHEET)
        _writer.WriteValue(columnWidth)
        _writer.WriteEndAttribute()
        _writer.WriteEndElement()
    End Sub

#Region "Write Document/ Excel Base"
    Public Sub WriteStartDocument()
        If _writer Is Nothing Then
            Throw New InvalidOperationException("Cannot write after closing.")
        End If

        _writer.WriteProcessingInstruction("mso-application", "progid=""Excel.Sheet""")
        _writer.WriteStartElement("Workbook", Const_SHEET)
        _writer.WriteAttributeString("xmlns", "o", Nothing, Const_OFFICE)
        _writer.WriteAttributeString("xmlns", "x", Nothing, Const_EXCEL)
        _writer.WriteAttributeString("xmlns", "ss", Nothing, Const_SHEET)
        WriteExcelStyles()
        WriteExcelDocumentProperties()
    End Sub

    Private Sub WriteExcelDocumentProperties()
        With _writer
            .WriteStartElement("ExcelWorkbook", Const_EXCEL)
            .WriteStartElement("ProtectStructure")
            .WriteString("False")
            .WriteEndElement()
            .WriteStartElement("ProtectWindows")
            .WriteString("False")
            .WriteEndElement()
            .WriteEndElement()
        End With


    End Sub
#Region "Write Worksheets"

    Public Sub WriteStartWorksheet(name As String)
        If _writer Is Nothing Then
            Throw New InvalidOperationException("Cannot write after closing.")
        End If

        _writer.WriteStartElement("Worksheet")
        _writer.WriteAttributeString("Name", Const_SHEET, name)
        _writer.WriteStartElement("Table")
    End Sub

#Region "Write Rows"
    Public Sub WriteStartRow()
        If _writer Is Nothing Then
            Throw New InvalidOperationException("Cannot write after closing.")
        End If
        _writer.WriteStartElement("Row")
    End Sub

#Region "Write Cells"
    Public Sub WriteExcelStyledCell(value As Object, style As CellStyle)
        If _writer Is Nothing Then
            Throw New InvalidOperationException("Cannot write after closing.")
        End If

        _writer.WriteStartElement("Cell")
        _writer.WriteAttributeString("StyleID", Const_SHEET, style.ToString())
        _writer.WriteStartElement("Data", Const_SHEET)
        Select Case style
            Case CellStyle.General
                _writer.WriteAttributeString("Type", Const_SHEET, "String")
                Exit Select
            Case CellStyle.Number, CellStyle.Currency
                _writer.WriteAttributeString("Type", Const_SHEET, "Number")
                Exit Select
            Case CellStyle.ShortDate, CellStyle.DateTime
                _writer.WriteAttributeString("Type", Const_SHEET, "DateTime")
                Exit Select
        End Select
        _writer.WriteValue(value)
        '  tag += String.Format("{1}\"><ss:Data ss:Type=\"DateTime\">{0:yyyy\\-MM\\-dd\\THH\\:mm\\:ss\\.fff}</ss:Data>", value,

        _writer.WriteEndElement()
        _writer.WriteEndElement()
    End Sub

    Public Sub WriteExcelUnstyledCell(value As String)
        If _writer Is Nothing Then
            Throw New InvalidOperationException("Cannot write after closing.")
        End If

        _writer.WriteStartElement("Cell")
        _writer.WriteStartElement("Data")
        _writer.WriteAttributeString("Type", Const_SHEET, "String")
        _writer.WriteValue(value)
        _writer.WriteEndElement()
        _writer.WriteEndElement()
    End Sub

    Public Sub WriteExcelAutoStyledCell(value As Object)
        If _writer Is Nothing Then
            Throw New InvalidOperationException("Cannot write after closing.")
        End If

        'write the <ss:Cell> and <ss:Data> tags for something
        If TypeOf value Is Int16 OrElse TypeOf value Is Int32 OrElse TypeOf value Is Int64 OrElse TypeOf value Is [SByte] OrElse TypeOf value Is UInt16 OrElse TypeOf value Is UInt32 OrElse TypeOf value Is UInt64 OrElse TypeOf value Is [Byte] Then
            WriteExcelStyledCell(value, CellStyle.Number)
        ElseIf TypeOf value Is [Single] OrElse TypeOf value Is [Double] OrElse TypeOf value Is [Decimal] Then
            'we'll assume it's a currency
            WriteExcelStyledCell(value, CellStyle.Currency)
        ElseIf TypeOf value Is DateTime Then
            'check if there's no time information and use the appropriate style
            WriteExcelStyledCell(value, If(CType(value, DateTime).TimeOfDay.CompareTo(New TimeSpan(0, 0, 0, 0, 0)) = 0, CellStyle.ShortDate, CellStyle.DateTime))
        Else
            WriteExcelStyledCell(value, CellStyle.General)
        End If
    End Sub
#End Region

    Public Sub WriteEndRow()
        If _writer Is Nothing Then
            Throw New InvalidOperationException("Cannot write after closing.")
        End If

        _writer.WriteEndElement()
    End Sub

#End Region

    Public Sub WriteEndWorksheet()
        If _writer Is Nothing Then
            Throw New InvalidOperationException("Cannot write after closing.")
        End If

        _writer.WriteEndElement()
        _writer.WriteEndElement()
    End Sub

#End Region

    Public Sub WriteEndDocument()
        If _writer Is Nothing Then
            Throw New InvalidOperationException("Cannot write after closing.")
        End If
        _writer.WriteEndElement()
    End Sub
#End Region

#Region "IDisposable Members"

    Public Sub Dispose() Implements IDisposable.Dispose
        If _writer Is Nothing Then
            Return
        End If

        _writer.Close()
        _writer = Nothing
    End Sub

#End Region
End Class
